In September 1999 NASA's Mars Climate Orbiter reached Mars on a path that took it too close to the planet, and contact was lost. The investigation found that one piece of ground software reported thruster impulse in pound-force seconds while the navigation software expected newton-seconds. Every number in the pipeline was a perfectly valid number. Nothing was missing, nothing was misspelled. The numbers just did not mean what the next program thought they meant.
That is the everyday danger of data. A model will happily fit whatever you feed it, and it will not tell you that a column is in cents, that a blank means "refused to answer", or that the same city is spelled three ways. The people who catch these things are not running anything clever. They are reading the table, asking what each column means, and drawing a picture before they trust a summary.
Practitioners say this is most of the job. The often-quoted figure that it is "80 per cent" is a rule of thumb, not a measurement, but anyone who has worked with real data will recognise the proportion. This piece is about that part of the job: the shape data should have, the habits of cleaning it, what to do about holes, and the four ways a quick summary lies to you.
Before cleaning, there is the question of layout. A table can hold the same facts in many arrangements, and some make the next step easy while others make it a chore. The rule most data scientists work to was set out by Hadley Wickham in a 2014 paper called "Tidy Data": each variable is a column, each observation is a row, and each kind of thing you measured gets its own table.
Spreadsheets for people tend to be wide, with months running across the top, because that is easy to read. Tools for analysis prefer long, because "group by month" is then a single instruction on a single column. Try the switch below, then add a month.
Illustrative figures for three invented shops.
Without JavaScript: the long table has three columns (shop, month, sales) and nine rows for three months; adding a month adds three rows and changes no formula, while the wide table gains a column and every formula that listed the months must be edited.
The payoff is not tidiness for its own sake. When the shape is right, a month added next quarter does not break anything, and the same few operations (filter, group, summarise, join) work on every table you meet.
Real tables arrive with the problems below, and almost always more than one at once. Duplicate rows, because an export ran twice. The same category spelled several ways. A number stored as text, with a decimal comma in one country and a point in another. Mixed units, such as dollars in most rows and cents in a few. A placeholder such as 999999 standing for "unknown". Each of these quietly wrecks an average.
The discipline is to fix them one at a time, in code, and to write down what you did and why. Step through a small, invented order table below and watch the mean order value change as each fix is applied.
Invented orders, labelled illustrative. "Naive" means every amount is read with a plain number parser.
Without JavaScript: read naively, the thirteen raw rows give a mean order of tens of thousands of dollars because of one placeholder (999999) and one amount in cents. After removing the duplicate, standardising the names, fixing the decimal comma and cents, and treating 999999 and the blank as missing, ten amounts remain with a mean of 23.58.
Two habits matter more than any technique. First, never overwrite the raw file: keep the original, and write the cleaning as a script that turns raw into clean, so anyone can rerun it. Second, keep the log. In 2013 three economists, Herndon, Ash and Pollin, re-examined the spreadsheet behind a widely cited 2010 economics paper by Reinhart and Rogoff. They found that a formula had left five countries out of an average, along with other choices about which data to include. Corrected, the average growth for the high-debt group moved from −0.1 per cent to 2.2 per cent. Reinhart and Rogoff acknowledged the coding error while disputing the other criticisms and the strength of the conclusions drawn, so scholars still argue about the economics. The lesson for a data scientist is narrower: a result that lives only inside a hand-edited spreadsheet cannot be checked.
Spreadsheets cause quieter trouble too. A 2016 scan of genomics papers found that about one in five with gene lists in Excel files contained gene names the program had silently turned into dates, so that a gene called SEPT2 became "2-Sep". In 2020 England's public-health agency lost track of nearly 16,000 positive Covid-19 test results because lab files were collected into an older spreadsheet format with a row limit. None of these is a failure of intelligence. They are what happens when the cleaning is invisible.
Almost every real table has holes. The first instinct, to delete rows with blanks or fill them with the average, is only sometimes harmless. Donald Rubin's 1976 framework sorts missingness into three kinds, and the right move depends on which one you have.
The simulation below builds 3,000 invented respondents whose income rises with age, deletes a share of the incomes by each mechanism, and tries two repairs. You will see the bias in the average, and a second cost of "fill with the mean": it leaves the average alone but shrinks the spread, because the filled-in values are all identical.
Invented data: age 20–65, income in thousands rising about 2% a year of age plus a random spread. Bias is estimate minus the true mean of all 3,000 incomes.
Without JavaScript: in a typical run with 30 per cent missing, deleting blanks leaves the mean about right under MCAR, too high under MAR (young, lower earners are missing) and too low under MNAR (high earners are missing). Filling by age band repairs MAR but not MNAR.
The practical rule: ask who is missing, and write down your answer. Modern statistics offers better tools than deletion or mean-filling, such as multiple imputation, but they too rest on an assumption about why values are absent, and no software can test the MNAR case from the data alone.
Once the table is clean you will want to summarise it with a mean, a spread and a correlation. These are useful and also dangerous, because very different data can share them. In 1973 the statistician Francis Anscombe made the point with four small datasets of eleven points each. All four have the same mean of x (9), the same mean of y (7.5), the same variances, the same correlation (0.816), and the same best-fitting line, y = 3 + 0.5x. Plotted, they look nothing alike.
Without JavaScript: set I is a noisy straight line; II is a smooth curve; III is a perfect line with one outlier that tilts the fit; IV is eleven points at one x value plus a single far point that alone creates the slope. All four report r = 0.82.
In 2017 Justin Matejka and George Fitzmaurice of Autodesk went further. Starting from a dinosaur-shaped scatter plot by Alberto Cairo, they used a search procedure to move points a little at a time while holding the summary statistics fixed to two decimal places, and produced a dozen very different shapes (a star, a ring, parallel lines) with the same numbers. The summaries did not change. The picture did. The habit this builds is simple: plot first, summarise second, and treat a single summary number as a claim that needs a picture to support it.
The most basic picture of one variable is the histogram, which counts how many observations fall in each bin. John Tukey's 1977 book Exploratory Data Analysis made this kind of looking a respectable part of statistics, not a warm-up. But a histogram has a setting that most tools pick for you: the width of the bins.
Too wide, and distinct groups merge into one hump. Too narrow, and chance wiggles look like structure. Rules exist for a sensible default (the Freedman–Diaconis rule sets the width to twice the interquartile range divided by the cube root of the number of observations), but they are starting points. Slide the width below on 240 invented delivery times and watch two groups of trips appear and vanish.
Invented delivery times for a courier: a mix of short hops and long trips, labelled illustrative.
Without JavaScript: the data are a blend of short trips (around 14 minutes) and long trips (around 31 minutes). Bins of 3 to 6 minutes show two humps; bins of 25 minutes or more show one lump.
Notice what the mean of this data is: about 22 minutes, a time hardly any trip took. A summary that describes nobody is a warning that you have mixed two groups.
The fourth lens is the group-by. A pattern in the whole can reverse inside every part. The standard real example comes from the fall 1973 admissions data at the University of California, Berkeley, analysed by Bickel, Hammel and O'Connell in Science in 1975. Pooled over the six largest departments, 44.5 per cent of male applicants were admitted and 30.4 per cent of female applicants. That looks like a serious gap against women.
Department by department, the picture turns over. In four of the six, women were admitted at a higher rate than men; in the other two men were slightly ahead. What happened is a matter of mix. Women applied far more to departments that admitted few applicants (such as C and E), and men to departments that admitted many (A and B). The authors concluded that the pooled gap did not show a bias in the way departments decided; it came from where people applied. Why the applications differed is a further question that this data does not answer.
Data: six largest departments, UC Berkeley, fall 1973 (the R UCBAdmissions table, from Bickel et al. 1975).
Without JavaScript: pooled, men 44.5% (1,198 of 2,691) and women 30.4% (557 of 1,835). By department, women's rates were higher in A, B, D and F and lower in C and E. Roughly half of male applicants (51.5%) applied to A or B, against 7.2% of female applicants.
You can put a number on the mix effect. Give women the same department mix as the men had, keeping each department's female admission rate, and the female rate would be 51.6 per cent, above the men's 44.5. The lesson is not "the data hide the truth". It is that a pooled number answers a different question from a per-group number, and you should look at both before saying which question you are asking.
This is also where exploration meets causation. The department is a variable that sits between the applicant and the decision, and whether to adjust for it depends on a causal story. Experiments & Causal Inference (36/34) takes that on.
Data does not arrive ready to model. Give it a tidy shape, clean it with a script and a log, ask why values are missing before choosing a fix, and draw the picture before trusting a summary. The few hours spent here decide whether any later model means anything.
Try this: open any spreadsheet you use, pick one column, and write down its unit and what a blank would mean. Then sort it, look at the smallest and largest five values, and ask whether each is believable.
Sources. NASA/JPL Mars Climate Orbiter Mishap Investigation Board, Phase I report (1999); Wickham, "Tidy Data," Journal of Statistical Software 59(10), 2014; Herndon, Ash and Pollin, "Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff," Cambridge Journal of Economics (2014; working paper 2013); Reinhart and Rogoff, "Growth in a Time of Debt," American Economic Review 100(2), 2010; Ziemann, Eren and El-Osta, "Gene name errors are widespread in the scientific literature," Genome Biology 17, 2016; BBC News, "Excel: Why using Microsoft's tool caused Covid-19 results to be lost," 5 October 2020; Rubin, "Inference and missing data," Biometrika 63, 1976; Anscombe, "Graphs in statistical analysis," The American Statistician 27(1), 1973; Matejka and Fitzmaurice, "Same stats, different graphs," CHI 2017; Tukey, Exploratory Data Analysis, 1977; Freedman and Diaconis, "On the histogram as a density estimator," 1981; Bickel, Hammel and O'Connell, "Sex bias in graduate admissions: data from Berkeley," Science 187, 1975.